﻿
using System;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace KD.Sms
{
    public class NPOIHelper
    {
        #region   文件属性信息
        static string companyName = "湖南科迪互联信息科技有限公司";                 //公司名称
        static string subject = "采样数据";                                         //主题名称

        #endregion


        /// <summary>
        /// Excel文件设置与数据处理
        /// </summary>
        /// <param name="table">数据表</param>
        /// <param name="headerText">头部文本</param>
        /// <param name="sheetName">表的名称</param>
        /// <param name="columnName">数据列名称</param>
        /// <param name="columnTitle">表标题</param>
        /// <returns></returns>
        public static HSSFWorkbook ExcelSettingsAndHandleData(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet = hssfworkbook.CreateSheet(sheetName);

            #region 设置文件属性信息

            //创建一个文档摘要信息实体。
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = companyName; //公司名称
            hssfworkbook.DocumentSummaryInformation = dsi;

            //创建一个摘要信息实体。
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = subject;
            si.Author = CacheUser.Chinese_name;// author;
            si.Title = headerText;
            si.Subject = headerText;
            si.CreateDateTime = DateTime.Now;
            hssfworkbook.SummaryInformation = si;

            #endregion

            ICellStyle dateStyle = hssfworkbook.CreateCellStyle();
            IDataFormat format = hssfworkbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 取得列宽

            int[] colWidth = new int[columnName.Length];
            for (int i = 0; i < columnName.Length; i++)
            {
                colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length;
            }
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < columnName.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length;
                    if (intTemp > colWidth[j])
                    {
                        colWidth[j] = intTemp;
                    }
                }
            }

            #endregion

            int rowIndex = 0;
            foreach (DataRow row in table.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    if (!string.IsNullOrEmpty(headerText))
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(headerText);

                        ICellStyle headStyle = hssfworkbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = hssfworkbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, table.Columns.Count - 1));


                    }
                    #endregion

                    #region 列头及样式
                    {
                        IRow headerRow;

                        headerRow = sheet.CreateRow(string.IsNullOrEmpty(headerText) ? 0 : 1);

                        ICellStyle headStyle = hssfworkbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = hssfworkbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        for (int i = 0; i < columnName.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(columnTitle[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            if ((colWidth[i] + 1) * 256 > 30000)
                            {
                                sheet.SetColumnWidth(i, 10000);
                            }
                            else
                            {
                                sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256);
                            }
                        }

                    }
                    #endregion

                    rowIndex = string.IsNullOrEmpty(headerText) ? 1 : 2;


                }
                #endregion

                #region 填充数据

                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int i = 0; i < columnName.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[columnName[i]].ToString();

                    switch (table.Columns[columnName[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            if (drValue.ToUpper() == "TRUE")
                                newCell.SetCellValue("是");
                            else if (drValue.ToUpper() == "FALSE")
                                newCell.SetCellValue("否");
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV.ToString("yyyy-MM-dd HH:mm:ss"));

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            if (boolV)
                                newCell.SetCellValue("是");
                            else
                                newCell.SetCellValue("否");
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }

                #endregion

                rowIndex++;
            }

            return hssfworkbook;
        }


        /// <summary>
        /// 将数据写入Excel文件
        /// </summary>
        /// <param name="hssfworkbook">工作簿</param>
        /// <param name="exportPath">文件导出路径</param>
        private static void WirteToFile(HSSFWorkbook hssfworkbook, string exportPath)
        {
            FileStream file = null;
            try
            {
                file = new FileStream(exportPath, FileMode.Create);
                hssfworkbook.Write(file);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("文件导出出错了！");
            }
            finally
            {
                file.Dispose();
                hssfworkbook.Close();
            }

        }

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <param name="table">数据表</param>
        /// <param name="headerText">头部文本。</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="columnName">数据列对应字段名称。</param>
        /// <param name="columnTitle">数据列对应字段的中文名称</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="exportFilePath">导出的文件路径</param>
        public static void ExportExcel(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle, string fileName, string exportFilePath)
        {
            //Excel文件设置与数据处理
            HSSFWorkbook hssfworkbook = ExcelSettingsAndHandleData(table, headerText, sheetName, columnName, columnTitle);
            //将数据写入Excel文件
            WirteToFile(hssfworkbook, exportFilePath );
        }

    }
}
